package com.poet.df.dbinfo.support;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.stereotype.Component;

import com.poet.df.dbinfo.pojo.Column;
import com.poet.df.dbinfo.pojo.ForeignKey;
import com.poet.df.dbinfo.pojo.PrimaryKey;
import com.poet.df.dbinfo.pojo.Table;

@Component("dbInfo")
public class ConfigurableDbInfoImpl extends AbstractConfigurableDbInfo implements InitializingBean {

	@Override
	public void afterPropertiesSet() throws Exception {
		super.afterPropertiesSet();
		this.loadDataBaseInfos();
	}

	private static Logger logger = Logger.getLogger(ConfigurableDbInfoImpl.class);
	
	private Object monitor = new Object();

	private String catalog;
	private String schema;

	
	@Override
	public void loadDataBaseInfos() {

		synchronized (this.monitor) {
			if (super.tablesCache == null) {
				tablesCache = new LinkedHashMap<String, Table>();
			} else {
				return ;
			}

			try (Connection connection = super.dataSource.getConnection()) {

				//init(connection);
				
				// 获取数据库元数据
				DatabaseMetaData metaData = connection.getMetaData();

				// 获取数据库表数据
				fetchTables(metaData);

				// 获取数据库中表所有列
				fetchColumns(metaData);
				
				// 获取主键
				fetchPrimaryKeys(metaData);
				
				// 获取外键
				fetchForeignKeys(metaData);

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取外键
	 * @param metaData
	 */
	private void fetchForeignKeys(DatabaseMetaData metaData) {
		for (Table table : super.tablesCache.values()) {
			String tableName = table.getTableName();
			String catalog = table.getCatalog();
			String schema = table.getSchema();
			ForeignKey fk = null;
			Column column = null;
			Map<String, ForeignKey> foreignKeys = table.getForeignKeys();
			Map<String, Column> columns = table.getColumns();
			try (ResultSet rs = metaData.getImportedKeys(catalog, schema,
					tableName)) {

				while (rs.next()) {
					String fkName = rs.getString("FK_NAME");
					String pkCatalog = rs.getString("PKTABLE_CAT");
					String pkSchema = rs.getString("PKTABLE_SCHEM");
					String pkTableName = rs.getString("PKTABLE_NAME");
					String pkColumnName = rs.getString("PKCOLUMN_NAME");
					String fkCatalog = rs.getString("FKTABLE_CAT");
					String fkSchema = rs.getString("FKTABLE_SCHEM");
					String fkTableName = rs.getString("FKTABLE_NAME");
					String fkColumnName = rs.getString("FKCOLUMN_NAME");
					
					fk = new ForeignKey();
					fk.setFkName(fkName);
					fk.setPkCatalog(pkCatalog);
					fk.setPkSchema(pkSchema);
					fk.setPkTableName(pkTableName);
					fk.setPkColumnName(pkColumnName);
					fk.setFkCatalog(fkCatalog);
					fk.setFkSchema(fkSchema);
					fk.setFkTableName(fkTableName);
					fk.setFkColumnName(fkColumnName);
					
					column = table.getColumns().get(fkColumnName);
					
					fk.setColumnName(column.getColumnName());
					fk.setColumnSize(column.getColumnSize());
					fk.setDataType(column.getDataType());
					fk.setTypeName(column.getTypeName());
					fk.setDecimalDigits(column.getDecimalDigits());
					fk.setNullable(column.getIsNullable());
					fk.setAutoIncrement(column.getIsAutoIncrement());
					fk.setRemarks(column.getRemarks());
					
					foreignKeys.put(fkColumnName, fk);
					columns.put(fkColumnName, fk);
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public void refreshDataBaseInfos() {
		super.tablesCache.clear();
		this.loadDataBaseInfos();
	}

	@Override
	public Table getTableInfo(String tableName) {
		return super.tablesCache.get(tableName);
	}

	@Override
	public List<Table> getAllTables() {
		return new ArrayList<Table>(super.tablesCache.values());
	}

	/**
	 * 初始化 catalog,schema
	 * 
	 * @param connection
	 */
	private void init(Connection connection) {
		try {
			this.catalog = connection.getCatalog();
			this.schema = connection.getSchema();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 提取所有的主键
	 */
	private void fetchPrimaryKeys(DatabaseMetaData metaData) {
		for (Table table : super.tablesCache.values()) {
			String tableName = table.getTableName();
			String catalog = table.getCatalog();
			String schema = table.getSchema();
			Map<String, Column> columns = table.getColumns();
			Map<String, PrimaryKey> primaryKeys = table.getPrimaryKeys();
			try (ResultSet rs = metaData.getPrimaryKeys(catalog, schema,
					tableName)) {
				PrimaryKey pk = null;
				Column column = null;
				while (rs.next()) {
					String columnName = rs.getString("COLUMN_NAME");
					column = columns.get(columnName);

					String pkName = rs.getString("PK_NAME");
					int keySeq = rs.getInt("KEY_SEQ");

					pk = new PrimaryKey();
					pk.setPkName(pkName);
					pk.setKeySeq(keySeq);

					pk.setColumnName(column.getColumnName());
					pk.setColumnSize(column.getColumnSize());
					pk.setDataType(column.getDataType());
					pk.setTypeName(column.getTypeName());
					pk.setDecimalDigits(column.getDecimalDigits());
					pk.setNullable(column.getIsNullable());
					pk.setAutoIncrement(column.getIsAutoIncrement());
					pk.setRemarks(column.getRemarks());

					columns.put(columnName, pk);
					primaryKeys.put(columnName, pk);

				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	/**
	 * 获取所有列
	 * 
	 * @param metaData
	 */
	private void fetchColumns(DatabaseMetaData metaData) {
		for (Table table : super.tablesCache.values()) {
			String tableName = table.getTableName();
			String catalog = table.getCatalog();
			String schema = table.getSchema();
			Map<String, Column> columns = table.getColumns();
			try (ResultSet rs = metaData.getColumns(catalog, schema, tableName,
					null)) {
				Column column = null;
				while (rs.next()) {
					String columnName = rs.getString("COLUMN_NAME");
					int dataType = rs.getInt("DATA_TYPE");
					String typeName = rs.getString("TYPE_NAME");
					int columnSize = rs.getInt("COLUMN_SIZE");
					int decimalDigits = rs.getInt("DECIMAL_DIGITS");
					int nullable = rs.getInt("NULLABLE");
					String remarks = rs.getString("REMARKS");
					String isAutoIncrement = rs.getString("IS_AUTOINCREMENT");

					column = new Column();
					column.setColumnName(columnName);
					column.setDataType(dataType);
					column.setTypeName(typeName);
					column.setColumnSize(columnSize);
					column.setDecimalDigits(decimalDigits);
					column.setRemarks(remarks);
					column.setNullable((nullable == 1 ? true : false));
					column.setAutoIncrement((isAutoIncrement.equals("YES") ? true
							: false));
					columns.put(columnName, column);
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取数据库表数据
	 * 
	 * @param metaData
	 */
	private void fetchTables(DatabaseMetaData metaData) {

		// 暂时没添加表名称过滤功能
		try (ResultSet rs = metaData.getTables(catalog, schema, super.tableNamePattern,
				super.types)) {
			Table table = null;
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				String catalog = rs.getString("TABLE_CAT");
				String schema = rs.getString("TABLE_SCHEM");
				String remarks = rs.getString("REMARKS");
				String tableType = rs.getString("TABLE_TYPE");

				table = new Table();
				table.setTableName(tableName);
				table.setCatalog(catalog);
				table.setSchema(schema);
				table.setRemarks(remarks);
				table.setTableType(tableType);
				super.tablesCache.put(tableName, table);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
